package com.sinog.biz.schedule;

import com.sinog.biz.BizApplication;
import com.sinog.biz.config.ProvinceCodesConfig;
import com.sinog.biz.config.RedisConfig;
import com.sinog.biz.interceptor.CodesHelper;
import com.sinog.biz.util.DataSourceCheckUtils;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.autoconfigure.condition.ConditionalOnProperty;
import org.springframework.context.ApplicationContext;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Component;
import redis.clients.jedis.Jedis;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

/**
 * @desc 系统管理统计 sfbadmin
 * 统计每天推送到司法部的表的数量和本省数量的差异---全量查询
 * @author 潘志成
 * @date 2022/8/10 16:21
 */
@Slf4j
@Component
@Configuration
@ConditionalOnProperty(name = "gkzx.azbj.census.open", havingValue = "true")
public class XtglCensusSchedule {

    private JdbcTemplate buTemplate;
    private JdbcTemplate jdbcTemplate;
    @Autowired
    private RedisConfig redisConfig;
    private static final Map<String,String> URL_MAP = new HashMap<>(64);

    /**
     * 向司法部的tbsys_count表中同步今天本省和司法部关于本省数据每张表的数量
     */
    @Scheduled(cron = "${gkzx.azbj.census.cron}")
    public void excludeTable() {
        log.info(">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>XtglCensusSchedule--------开始>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>");
        try(Jedis jedis = redisConfig.redisPoolFactory().getResource()) {
            String redisKey = "tbsys_count";
            String redisValue = "111";
            Long setnx = jedis.setnx(redisKey,redisValue);
            if(setnx == 1) {
                //设置过期时间
                jedis.setex(redisKey,60,redisValue);
                //读取application-*.yml配置文件中属性
                ProvinceCodesConfig provinceCodesConfig = CodesHelper.getInstance();
                List<Map<String,String>> valueMapList = provinceCodesConfig.getValueMapList();
                for(Map<String,String> m : valueMapList) {
                    URL_MAP.putAll(m);
                }
                //获取不同步司法部的表名
                List<String> excludeTable = new ArrayList<>(Arrays.asList(URL_MAP.get("exclude-tables").split(",")));
                //获取省份编码
                String localCode = URL_MAP.get("local-code");
                //省份编码前两位
                String proId = localCode.substring(0,2);
                //获取省份名称
                String selectProName = "select code_name from tbsys_code tc where code_type ='2_1' and code_id = " + localCode;
                ApplicationContext applicationContext = BizApplication.applicationContext;
                //从applicationContext中获取两个数据源，一个是本地的jdbcTemplate，一个是部级的bujiJdbcTemplate，关于对象的创建是在DasourceConfig中进行的
                buTemplate = (JdbcTemplate)applicationContext.getBean("bujiJdbcTemplate");
                jdbcTemplate = (JdbcTemplate)applicationContext.getBean("jdbcTemplate");
                //检测全部数据源是否可用
                boolean allRunning = DataSourceCheckUtils.checkRunningStatus(buTemplate,jdbcTemplate);
                if(true){
                    log.info(">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>XtglCensusSchedule--------获取数据源成功>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>");
                    //获取数据库名称，三种方式：
                    //方式一：直接读取配置文件，获取spring.datasource.url和spring.buji.url
                    //方式二：通过jdbcTemplate.getDataSource().getConnection().getMetaData().getURL();然后将获取的url进行拆分，获取数据库名，这种方式可能耗性能
                    //方式三：直接写sql，去查询数据库名，如下所示
                    //部级数据库名
                    String buSchema = "";
                    //省级数据库名
                    String proSchema = "";
                    String selectSchema = "SELECT DATABASE()";
                    if(buTemplate != null) {
                        buSchema = buTemplate.queryForObject(selectSchema,String.class);
                    }
                    if(jdbcTemplate != null) {
                        proSchema = jdbcTemplate.queryForObject(selectSchema,String.class);
                    }
                    //省份名称
                    String proName = jdbcTemplate.queryForObject(selectProName,String.class);
                    String selectProAllTables = "SELECT TABLE_NAME FROM information_schema.TABLES  WHERE TABLE_SCHEMA = '" + proSchema + "'";
                    String selectBuAllTables = "SELECT TABLE_NAME FROM information_schema.TABLES  WHERE TABLE_SCHEMA = '" + buSchema + "'";
                    //查询省份数据库中的所有表
                    List<String> proTables = jdbcTemplate.queryForList(selectProAllTables,String.class);
                    //查询部级数据库数据库中的所有表
                    List<String> buTables = buTemplate.queryForList(selectBuAllTables,String.class);
                    //排除掉不需要同步司法部的表
                    for(String e : excludeTable) {
                        proTables.remove(e);
                    }
                    //排除掉省份有，而部级没有的表（例如hn_interface_opposite这种）
                    //这里要使用临时变量来进行操作，不然会抛异常,直接赋值不行，使用的是同一个地址，还是会抛异常
                    List<String> temp = new ArrayList<>(proTables);
                    for(String table : temp) {
                        if(!buTables.contains(table)) {
                            proTables.remove(table);
                        }
                    }
                    log.info(">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>XtglCensusSchedule--------排除掉省份和部级不同的表之后剩余:{}张表>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>",proTables.size());
                    //存储当前省份每张表的数量，格式为 表名 ： 数量
                    Map<String,Object> proParam = new HashMap<>(16);
                    //存储部级每张表按省份的数量，格式为 表名 ： 数量
                    Map<String,Object> buParam = new HashMap<>(16);
                    //遍历所有表，进行统计
                    for(String table : proTables) {
                        //查询当前表中是否有分组标识(区分是哪个省份的标识)，可以用于分组的标识有pri_num,pid,PriNum,PROVINCE和session_id
                        String proGroup = "select COLUMN_NAME from information_schema.`COLUMNS` where TABLE_SCHEMA = '" + proSchema + "' and TABLE_NAME = '" + table + "' and (COLUMN_NAME = 'pri_num' or COLUMN_NAME = 'PROVINCE'or COLUMN_NAME = 'session_id')";
                        String buGroup = "select COLUMN_NAME from information_schema.`COLUMNS` where TABLE_SCHEMA = '" + buSchema + "' and TABLE_NAME = '" + table + "' and (COLUMN_NAME = 'pri_num'or COLUMN_NAME = 'PID'or COLUMN_NAME = 'PriNum'or COLUMN_NAME = 'PROVINCE'or COLUMN_NAME = 'session_id')";
                        List<String> buFlag = buTemplate.queryForList(buGroup,String.class);
                        List<String> proFlag = jdbcTemplate.queryForList(proGroup,String.class);
                        //部级
                        String countSql;
                        //省级
                        String countSql1;
                        //省级----统计省份可分组表数量
                        if(proFlag.size() != 0) {
                            if (proFlag.contains("pri_num")) {
                                countSql1 = "select count(1) as " + table + " from " + table + " where left(pri_num,2) = " + proId;
                            } else if (proFlag.contains("province")) {
                                countSql1 = "select count(1) as " + table + " from " + table + " where left(province,2) = " + proId;
                            } else if(proFlag.contains("session_id")) {
                                countSql1 = "select count(1) as " + table + " from " + table + " where left(session_id,2) = " + proId;
                            } else {
                                countSql1 = "select count(1) as " + table + " from " + table;
                            }
                        }else {
                            countSql1 = "select count(1) as " + table + " from " + table;
                        }
                        //部级---系统表不进行分组(区分是哪个省份的标识)(省份和部级表字段不一致，需要区分)
                        if(buFlag.size() != 0 && !table.contains("tbsys")) {
                            if(buFlag.contains("pri_num")) {
                                countSql = "select count(1) as " + table + " from " + table + " where left(pri_num,2) = " + proId;
                            } else if(buFlag.contains("PriNum")) {
                                countSql = "select count(1) as " + table + " from " + table + " where left(PriNum,2) = " + proId;
                            } else if(buFlag.contains("PID")) {
                                countSql = "select count(1) as " + table + " from " + table + " where left(pid,2) = " + proId;
                            } else if(buFlag.contains("PROVINCE")) {
                                countSql = "select count(1) as " + table + " from " + table + " where left(PROVINCE,2) = " + proId;
                            } else if(buFlag.contains("session_id")) {
                                countSql = "select count(1) as " + table + " from " + table + " where left(session_id,2) = " + proId;
                            } else {
                                countSql = "select count(1) as " + table + " from " + table;
                            }
                        } else {
                            countSql = "select count(1) as " + table + " from " + table;
                        }
                        buParam.putAll(buTemplate.queryForMap(countSql));
                        proParam.putAll(jdbcTemplate.queryForMap(countSql1));
                    }
                    Set<String> keys = buParam.keySet();
                    //在插入之前，删除之前的数据
                    String deleteSql = "delete from tbsys_count where proId = '" + proId + "'";
                    int i = buTemplate.update(deleteSql);
                    log.info(">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>XtglCensusSchedule--------执行完毕，删除数据成功，删除:{}条记录>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>",i);
                    //做日志记录用
                    i = 0;
                    for(String key : keys) {
                        String insertSql = "insert into tbsys_count(proId,proName,tableName,proCount,buCount)values('" + proId + "','" + proName + "','" + key + "'," + proParam.get(key) + "," + buParam.get(key) + ")";
                        i += buTemplate.update(insertSql);
                    }
                    log.info(">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>XtglCensusSchedule--------执行完毕，插入数据成功，插入:{}条记录>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>",i);
                } else {
                    log.error("检测到数据源不可用,本次执行任务失败!");
                }
            } else {
                try {
                    Thread.sleep(10000);
                } catch(InterruptedException e) {
                    throw new RuntimeException(e);
                }
            }
        }
    }
}